<?php
require_once "Spreadsheet/Excel/Writer.php"; 
//echo "<pre>";print_r($_REQUEST);exit("\n------");
	
	$gRow = 0 ;
	$gCol = 0 ;
	
	ini_set("max_execution_time", "1200" );
	ini_set("mssql.timeout", "1200" );
	set_time_limit("1200");
	
	$filename=$_SESSION["UserId"] . date("_Ymd_His") . ".xls";

	$xls =& new Spreadsheet_Excel_Writer(); 
	$xls->send($filename);
	
	$sheet =& $xls->addWorksheet('sheet1'); 

	//formato dei titoli 
	
	$title_format =& $xls->addFormat(); 
	//$title_format->setFgColor('blue');
	//$title_format->setColor('white');
	$title_format->setSize (10);
	$title_format->setBold (700); // 700 o anche 1 = bold , 400 o anche 0 = normal
	$title_format->setFontFamily ('Arial');
	$title_format->setAlign('center'); 
	$title_format->setTextWrap( ) ;
	
	$int_format =& $xls->addFormat();
	$int_format->setNumFormat('#,##0');
		
	$flo_format =& $xls->addFormat();
	$flo_format->setNumFormat('#,##0.00');
	
	$per_format =& $xls->addFormat();
	$per_format->setNumFormat('0.00%');
	
	$dat_format =& $xls->addFormat();
	$dat_format->setNumFormat('DD/MM/YYYY');
	 	 
	
	$sheet->setZoom(100);
	
	$panes[0]=1;
	$panes[1]=0;
	$panes[2]=true;
	$panes[3]=false;
	$panes[4]=2;
	$sheet->freezePanes($panes);	
	
	$rs = rs($sheetSql, $_REQUEST);
	
	if($rs->recordCount>0){
		//header
		showHeader($columns);
		while ($values = $rs->getRow()) {
			$GLOBALS["gCol"]=0;
			
			foreach($columns as $key=>$aVal){
				$function = $aVal["Format"];
				$function($values[$key]);
				$GLOBALS["gCol"]++;
			}
			
			$GLOBALS["gRow"]++;
		}
	}
	
	$xls->close(); 	
	
	// output Valore monetario
	function shVal($val){
		//return number_format($val, 0);  	
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$val,$GLOBALS["int_format"]);	
		return $val;  
	}

	function shPri($val){
		//return number_format($val, 3);  
		
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$val,$GLOBALS["flo_format"]);
		return $val;  
	}

	function shQty($val){
		//return number_format($val, 0);  
		
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$val,$GLOBALS["int_format"]);
		return $val;  
	}
	
	// output valore precentuale
	function shPer($val){
		//return number_format($val*100,2).' %';  
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$val,$GLOBALS["per_format"]);
		return ($val*100).' %';  
	}
	
	// output valore precentuale di differenza
	function shPerDif($va0, $va1){

		if ($va0<=0&&$va1<=0) {
			$out = 0;
		}elseif($va0==0&&$va1>0){
			$out = -1;
		}elseif($va0>0&&$va1==0){
			$out = 1;
		}else{
			$out = ($va0-$va1)/$va1;
		}
		
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$out,$GLOBALS["per_format"]);
		return shPer($out);
	}

	// output valore precentuale di differenza
	function shVCM($va0, $va1){


		if ($va0<=0&&$va1<=0) {
			$out = 0;
		}elseif($va0==0&&$va1>0){
			$out = -1;
		}elseif($va0>0&&$va1==0){
			$out = 1;
		}else{
			$out = ($va0-$va1)/$va0;
		}
		
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$out,$GLOBALS["per_format"]);
		return shPer($out);
	}
	
	function shPerDif2($va0, $va1){
		
		$out = 0;
		
		if ($va0>0&&$va1>0) {
		    $out = ($va0-$va1)/$va1;
		}
		
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$out,$GLOBALS["per_format"]);
		return shPer($out);
	}

	function shDim($val){
		
		$GLOBALS['sheet']->writeString($GLOBALS["gRow"] ,$GLOBALS["gCol"],$val);
		return $val.'&#160;';  
	}

	function shDta($val){

		if (empty($val)) {
		    return '';
		}
		$gRow = $GLOBALS["gRow"];
		$gCol = $GLOBALS["gCol"];
		$seconds_in_a_day = 86400;
		$ut_to_ed_diff = $seconds_in_a_day * 25569;
		
		$out = (mktime(4,0,0,substr($val,4,2),substr($val,6,2),substr($val,0,4)) + $ut_to_ed_diff) / $seconds_in_a_day ;
		$out = intval($out);
		$GLOBALS['sheet']->write($GLOBALS["gRow"] ,$GLOBALS["gCol"],$out,$GLOBALS["dat_format"]);
		return $out;
	}

	function showHeader(&$at){

		$k=0;
		
		//print_r($title_format);
		foreach ($at as $key=>$aVal) {
			$val = $aVal["Caption"];
			$size = intval($aVal["Width"]);
			
			$GLOBALS["sheet"]->writeString(0,$k,$val,$GLOBALS["title_format"]);
			$GLOBALS["sheet"]->setColumn( $k, $k, $size);
			
			$k++;
			
		}
		
		$GLOBALS["gRow"]++;
	}				
				
	
?>